﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;

using DataBaseInfo;
using SQLServerHelper;
using System.Collections;

namespace DataBaseHelper
{
    public partial class DataBaseHelperMain : Form
    {
        public SQLServerManger oManger;
        private Dictionary<string, List<Table>> aDataBaseList;
        private List<Table> aTable;
        private ControlType oControlType=ControlType.Select;

        private string sOutPutColumn, sInputValues,sInputWhere,sInputJoin,sInputOrder;

        private readonly string sSelectText = "SELECT \r\n$OutPutColumn$ \r\nFROM $MainTable$ \r\n$Join$ $Where$ $Order$";
        private readonly string sUpdateText = "UPDATE $Table$ \r\nSET $Value$ \r\n$Where$";
        private readonly string sDeleteText = "DELETE FROM $Table$ \r\n$Where$";
        private readonly string sInsertText = "INSERT INTO \r\n$Table$($Column$) \r\nvalues($Value$)";

        public DataBaseHelperMain()
        {
            InitializeComponent();
        }

        private void DataBaseHelperMain_Load(object sender, EventArgs e)
        {
            //取到所有数据库列表
            aDataBaseList = oManger.GetDataBaseList();
            cmb_DatabaseList.Items.Add("");
            foreach (KeyValuePair<string,List<Table>> oPair in aDataBaseList)
            {
                if (!string.IsNullOrEmpty(oPair.Key))
                    cmb_DatabaseList.Items.Add(oPair.Key);
            }
        }

        //选择相关的数据库 把相关表显示出来
        private void cmb_DatabaseList_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(cmb_DatabaseList.Text))
            {
                aTable = aDataBaseList[cmb_DatabaseList.Text];
                chkListbox_dataTableList.Items.Clear();
                chkListBox_TableColumn.Items.Clear();
                foreach (Table table in aTable)
                {
                    chkListbox_dataTableList.Items.Add(table.TableName);
                }
            }
        }

        private void ClearComboBox()
        {
            cmb_Column.Items.Clear();
            cmb_JoinColumn.Items.Clear();
            cmb_MainColumn.Items.Clear();
            cmb_MainTable.Items.Clear();
            cmb_JoinTable.Items.Clear();
            cmb_MainTable.Items.Clear();
            cmb_JoinTable.Items.Clear();
            cmb_OrderColumn.Items.Clear();
            cmb_OrderColumn.Items.Add("");
            cmb_Column.Items.Add("");
            cmb_JoinColumn.Items.Add("");
            cmb_MainColumn.Items.Add("");
            cmb_MainTable.Items.Add("");
            cmb_JoinTable.Items.Add("");
        }

        private void ClearAll()
        {
            //取消选择的表
            for (int i = 0; i < chkListbox_dataTableList.Items.Count; i++)
            {
                if (chkListbox_dataTableList.GetItemChecked(i))
                {
                    chkListbox_dataTableList.SetItemChecked(i, false);
                }
            }
            chkListBox_TableColumn.Items.Clear();
            cmb_Column.Items.Clear();
            cmb_JoinColumn.Items.Clear();
            cmb_JoinColumn.Text = "";
            cmb_MainColumn.Text = "";
            cmb_MainColumn.Items.Clear();
            cmb_MainTable.Items.Clear();
            cmb_JoinTable.Items.Clear();
            cmb_MainTable.Items.Clear();
            cmb_JoinTable.Items.Clear();
            cmb_OrderColumn.Items.Clear();
            cmb_WhereBracket.SelectedIndex = 0;
            cmb_WhereOperator.Items.Clear();
            txt_WhereValue.Text = "";
            cmb_OrderType.SelectedIndex = 0;
            cmb_LRight.SelectedIndex = 0;
            txt_SQL.Text = "";

            sOutPutColumn = sInputValues = sInputWhere = sInputJoin = sInputOrder = "";
        }

        //用户选择相关表 把相关表的字段显示出来
        private void chkListbox_dataTableList_SelectedIndexChanged(object sender, EventArgs e)
        {
            chkListBox_TableColumn.Items.Clear();
            ClearComboBox();
            for (int i = 0; i < chkListbox_dataTableList.Items.Count; i++)
            {
                if (chkListbox_dataTableList.GetItemChecked(i))
                {
                    List<Column> aColumn=aTable[i].GetColumns();
                    foreach (Column column in aColumn)
                    {
                        chkListBox_TableColumn.Items.Add(aTable[i].TableName + "." + column.ClumnName);
                        cmb_Column.Items.Add(new DictionaryEntry(aTable[i].TableName + "." + column.ClumnName, column.ClumnType));
                        cmb_OrderColumn.Items.Add(aTable[i].TableName + "." + column.ClumnName);
                        cmb_JoinColumn.Items.Add(aTable[i].TableName + "." + column.ClumnName);
                        cmb_MainColumn.Items.Add(aTable[i].TableName + "." + column.ClumnName);
                    }
                    
                    cmb_MainTable.Items.Add(chkListbox_dataTableList.Items[i].ToString());
                    cmb_JoinTable.Items.Add(chkListbox_dataTableList.Items[i].ToString());
                }                
            }
            cmb_Column.DisplayMember = "Key";
            cmb_Column.ValueMember = "Value";
            if (cmb_MainTable.Items.Count > 1)
                cmb_MainTable.SelectedIndex = 1;
            
            GenerateSQL();
        }

        private void GenerateSQL()
        {
            if (oControlType == ControlType.Insert)
                txt_SQL.Text = sInsertText.Replace("$Table$", cmb_MainTable.Text).Replace("$Column$", sOutPutColumn).Replace("$Value$", sInputValues);
            else if (oControlType == ControlType.Delete)
                txt_SQL.Text = sDeleteText.Replace("$Table$", cmb_MainTable.Text).Replace("$Where$", sInputWhere);
            else if (oControlType == ControlType.Update)
                txt_SQL.Text = sUpdateText.Replace("$Table$", cmb_MainTable.Text).Replace("$Value$", sInputValues).Replace("$Where$", sInputWhere);
            else
                txt_SQL.Text = sSelectText.Replace("$MainTable$", cmb_MainTable.Text).Replace("$OutPutColumn$", sOutPutColumn).Replace("$Where$", sInputWhere).Replace("$Join$", sInputJoin).Replace("$Order$",sInputOrder);

        }

        private void DataBaseHelperMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            Application.Exit();
        }

        private void chkListBox_TableColumn_SelectedIndexChanged(object sender, EventArgs e)
        {            
            sOutPutColumn = "";
            foreach (string item in chkListBox_TableColumn.CheckedItems)
            {
                sOutPutColumn += item.ToString() + ",";
            }
            sOutPutColumn = sOutPutColumn.TrimEnd(',');
            GenerateSQL();
        }

        //添加条件
        private void btn_AddWhere_Click(object sender, EventArgs e)
        {
            DictionaryEntry obj = (DictionaryEntry)cmb_Column.SelectedItem;
            string sColumnType = obj.Value.ToString();
            string sValue = txt_WhereValue.Text;
            if (sColumnType.Equals("varchar") || sColumnType.Equals("datetime"))
                sValue = "'" + sValue + "'";
            if (oControlType == ControlType.Insert)
            {
                if (!string.IsNullOrEmpty(sOutPutColumn) && !string.IsNullOrEmpty(sValue))
                {
                    sOutPutColumn += "," + cmb_Column.Text;
                    sInputValues += "," + sValue;
                }
                else
                {
                    sOutPutColumn += cmb_Column.Text;
                    sInputValues +=  sValue;
                }
            }
            else if (oControlType == ControlType.Select || oControlType == ControlType.Delete || (oControlType==ControlType.Update && rbtn_where.Checked))
            {                
                sInputWhere += cmb_WhereBracket.Text + " "+cmb_Column.Text + cmb_WhereOperator.Text + sValue + " ";
            }
            else if (oControlType == ControlType.Update && rbtn_Value.Checked)
            {
                if (!string.IsNullOrEmpty(sInputValues))
                    sInputValues += ","+cmb_Column.Text + "=" + sValue;
                else
                    sInputValues += cmb_Column.Text + "=" + sValue;
            }
            cmb_Column.SelectedIndex = 0;
            cmb_WhereOperator.SelectedIndex = 0;
            txt_WhereValue.Text = "";
            cmb_WhereBracket.SelectedIndex = 0;

            if (rbtn_where.Checked && sInputWhere.IndexOf("WHERE") <= 0)
                sInputWhere = " WHERE " + sInputWhere;

            GenerateSQL();
        }
        //添加排序
        private void btn_AddOrder_Click(object sender, EventArgs e)
        {            
            sInputOrder += cmb_OrderColumn.Text + " " + cmb_OrderType.Text + " ";
            cmb_OrderColumn.SelectedIndex = 0;
            cmb_OrderType.SelectedIndex = 0;

            if (sInputOrder.IndexOf("ORDERY BY") <= 0)
                sInputOrder = " ORDERY BY " + sInputOrder;

            GenerateSQL();
        }

        //对操作类型的操作
        private void SelectControlType_Click(object sender, EventArgs e)
        {
            sInputWhere = "";
            sInputValues = "";
            txt_SQL.Text = "";
            oControlType = GetControlType();
            QueryControl(oControlType);

            if (chkListbox_dataTableList.CheckedItems.Count > 1)
            {
                for (int i = 0; i < chkListbox_dataTableList.Items.Count; i++)
                {
                    if (chkListbox_dataTableList.GetItemChecked(i))
                    {
                        chkListbox_dataTableList.SetItemChecked(i, false);
                    }
                }
                chkListBox_TableColumn.Items.Clear();
            }

            GenerateSQL();
        }
        private ControlType GetControlType()
        {
            ControlType oControlType;
            if (rbtn_TypeSelect.Checked)
                oControlType = ControlType.Select;
            else if (rbtn_TypeADD.Checked)
                oControlType = ControlType.Insert;
            else if (rbtn_TypeUpdate.Checked)
                oControlType = ControlType.Update;
            else
                oControlType = ControlType.Delete;
            return oControlType;
        }
        enum ControlType
        {
            Select,
            Update,
            Insert,
            Delete
        }

        private void QueryControl(ControlType oControlType)
        {
            if (oControlType == ControlType.Insert)
            {
                cmb_WhereBracket.Enabled = false;
                groupBox_Column.Enabled = false;
                groupBox_Order.Enabled = false;
                groupBox_Join.Enabled = false;
                
                rbtn_Value.Checked = true;
                rbtn_Value.Enabled = true;
                rbtn_where.Enabled = false;
            }
            else if (oControlType == ControlType.Delete)
            {
                cmb_WhereBracket.Enabled = true;
                groupBox_Column.Enabled = false;
                groupBox_Order.Enabled = false;
                groupBox_Join.Enabled = false;

                rbtn_where.Checked = true;
                rbtn_where.Enabled = true;
                rbtn_Value.Enabled = false;
            }
            else if (oControlType == ControlType.Update)
            {
                cmb_WhereBracket.Enabled = true;
                groupBox_Column.Enabled = false;
                groupBox_Order.Enabled = false;
                groupBox_Join.Enabled = false;

                rbtn_Value.Checked = true;
                rbtn_Value.Enabled = true;
                rbtn_where.Enabled = true;
            }
            else
            {
                cmb_WhereBracket.Enabled = true;
                groupBox_Column.Enabled = true;
                groupBox_Order.Enabled = true;
                groupBox_Join.Enabled = true;

                rbtn_where.Checked = true;
                rbtn_where.Enabled = true;
                rbtn_Value.Enabled = false;
            }
            SetBracket();
        }

        private void SetValueOrWhere(object sender, EventArgs e)
        {
            SetBracket();
        }

        private void SetBracket()
        {
            if (rbtn_Value.Checked)
            {
                cmb_WhereBracket.Enabled = false;
            }
            else
                cmb_WhereBracket.Enabled = true;
        }

        private void btn_GenerateSQL_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txt_SQL.Text))
            {
                Clipboard.SetText(txt_SQL.Text);
                tss_Messinfo.Text = "拷贝成功!";
            }
            else
            {
                tss_Messinfo.Text = "SQL语句为空,Copy失败.";
            }
        }

        //动态设置运算符
        private void cmb_WhereColumn_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmb_Column.SelectedItem.ToString() != "")
            {
                DictionaryEntry obj = (DictionaryEntry)cmb_Column.SelectedItem;
                string sColumnType = obj.Value.ToString();
                SetOperator(oControlType, sColumnType);
            }
        }

        private void SetOperator(ControlType oControlType, string sColumnType)
        {
            cmb_WhereOperator.Items.Clear();
            if (oControlType == ControlType.Insert || oControlType==ControlType.Update)
            {
                cmb_WhereOperator.Items.Add("");
                cmb_WhereOperator.Items.Add("=");
            }
            else //TODO:判断字段类型 image text 
            {
                cmb_WhereOperator.Items.Add("");
                cmb_WhereOperator.Items.Add("=");
                cmb_WhereOperator.Items.Add(">");
                cmb_WhereOperator.Items.Add(">=");
                cmb_WhereOperator.Items.Add("<");
                cmb_WhereOperator.Items.Add("<=");
                cmb_WhereOperator.Items.Add("!=");
                cmb_WhereOperator.Items.Add("Like");
                cmb_WhereOperator.Items.Add("In");                
            }
        }

        private void chkListbox_dataTableList_ItemCheck(object sender, ItemCheckEventArgs e)
        {
            if (oControlType != ControlType.Select)
            {
                if (e.CurrentValue == CheckState.Checked) return;
                for (int i = 0; i < ((CheckedListBox)sender).Items.Count; i++)
                {
                    ((CheckedListBox)sender).SetItemChecked(i, false);
                }
                e.NewValue = CheckState.Checked; 
            }
        }

        private void btn_AddJoin_Click(object sender, EventArgs e)
        {
            sInputJoin += cmb_LRight.Text + " " + cmb_JoinTable.Text + " ON " + cmb_JoinColumn.Text + "=" + cmb_MainColumn.Text;
            GenerateSQL();

            cmb_LRight.SelectedIndex = 0;
            cmb_MainTable.SelectedIndex = 0;
            cmb_JoinTable.SelectedIndex = 0;
            cmb_JoinColumn.SelectedIndex = 0;
            cmb_MainColumn.SelectedIndex = 0;
        }

        private void btn_ClearAll_Click(object sender, EventArgs e)
        {
            ClearAll();
        }

        private void 重新连接ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataBaseLogin oLogin = new DataBaseLogin();
            oLogin.Show();
            this.Hide();
        }

        private void 退出程序ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }


    }
}
